package com.TeaManage.servlet;

import com.TeaManage.bean.*;
import com.TeaManage.util.GokJDBCUtil;

import javax.servlet.ServletException;
import javax.servlet.annotation.WebServlet;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@WebServlet("/purchase/*")
public class PurchaseServlet extends HttpServlet {

    @Override
    protected void doGet(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String pathInfo = request.getPathInfo();

        if (pathInfo == null) {
            response.sendRedirect(request.getContextPath() + "/purchase/list");
            return;
        }

        switch (pathInfo) {
            case "/list":
                handlePurchaseList(request, response);
                break;
            case "/add":
                handleAddPurchase(request, response);
                break;
            case "/edit":
                handleEditPurchase(request, response);
                break;
            case "/detail":
                handlePurchaseDetail(request, response);
                break;
            case "/suppliers":
                handleSupplierList(request, response);
                break;
            case "/warehouses":
                handleWarehouseList(request, response);
                break;
            case "/teas":
                handleTeaList(request, response);
                break;
            default:
                response.sendRedirect(request.getContextPath() + "/purchase/list");
                break;
        }
    }


    @Override
    protected void doPost(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        String pathInfo = request.getPathInfo();

        if (pathInfo == null) {
            response.sendRedirect(request.getContextPath() + "/purchase/list");
            return;
        }

        switch (pathInfo) {
            case "/save":
                handleSavePurchase(request, response);
                break;
            case "/update":
                handleUpdatePurchase(request, response);
                break;
            case "/delete":
                handleDeletePurchase(request, response);
                break;
            case "/updateStatus":
                handleUpdateStatus(request, response);
                break;
            default:
                response.sendRedirect(request.getContextPath() + "/purchase/list");
                break;
        }
    }

    // 处理采购单列表
    private void handlePurchaseList(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            String statusStr = request.getParameter("status");
            String searchKeyword = request.getParameter("search");
            String pageStr = request.getParameter("page");

            int page = 1;
            int pageSize = 10;

            if (pageStr != null && !pageStr.isEmpty()) {
                try {
                    page = Integer.parseInt(pageStr);
                } catch (NumberFormatException e) {
                    page = 1;
                }
            }

            Connection conn = GokJDBCUtil.getConnection();

            // 构建查询条件
            StringBuilder sql = new StringBuilder(
                "SELECT p.*, s.supplier_name, w.warehouse_name FROM purchase p " +
                "LEFT JOIN supplier s ON p.supplier_id = s.supplier_id " +
                "LEFT JOIN warehouse w ON p.warehouse_id = w.warehouse_id WHERE 1=1"
            );
            List<Object> params = new ArrayList<>();

            if (statusStr != null && !statusStr.isEmpty() && !statusStr.equals("-1")) {
                sql.append(" AND p.status = ?");
                params.add(Integer.parseInt(statusStr));
            }

            if (searchKeyword != null && !searchKeyword.trim().isEmpty()) {
                sql.append(" AND (p.purchase_no LIKE ? OR s.supplier_name LIKE ?)");
                String keyword = "%" + searchKeyword.trim() + "%";
                params.add(keyword);
                params.add(keyword);
            }

            // 获取总记录数
            String countSql = "SELECT COUNT(*) FROM (" + sql.toString() + ") AS temp";
            PreparedStatement countStmt = conn.prepareStatement(countSql);
            for (int i = 0; i < params.size(); i++) {
                countStmt.setObject(i + 1, params.get(i));
            }
            ResultSet countRs = countStmt.executeQuery();
            int totalRecords = 0;
            if (countRs.next()) {
                totalRecords = countRs.getInt(1);
            }
            countRs.close();
            countStmt.close();

            // 分页查询
            sql.append(" ORDER BY p.purchase_id DESC LIMIT ?, ?");
            params.add((page - 1) * pageSize);
            params.add(pageSize);

            PreparedStatement stmt = conn.prepareStatement(sql.toString());
            for (int i = 0; i < params.size(); i++) {
                stmt.setObject(i + 1, params.get(i));
            }

            ResultSet rs = stmt.executeQuery();
            List<Purchase> purchaseList = new ArrayList<>();

            while (rs.next()) {
                Purchase purchase = new Purchase();
                purchase.setPurchase_id(rs.getInt("purchase_id"));
                purchase.setSupplier_id(rs.getInt("supplier_id"));
                purchase.setWarehouse_id(rs.getInt("warehouse_id"));
                purchase.setPurchase_no(rs.getString("purchase_no"));
                purchase.setTotal_amount(rs.getDouble("total_amount"));
                purchase.setStatus(rs.getInt("status"));

                // 设置关联对象信息
                Supplier supplier = new Supplier();
                supplier.setSupplier_name(rs.getString("supplier_name"));
                purchase.setSupplier(supplier);

                Warehouse warehouse = new Warehouse();
                warehouse.setWarehouse_name(rs.getString("warehouse_name"));
                purchase.setWarehouse(warehouse);

                purchaseList.add(purchase);
            }

            rs.close();
            stmt.close();
            conn.close();

            // 计算分页信息
            int totalPages = (int) Math.ceil((double) totalRecords / pageSize);

            request.setAttribute("purchaseList", purchaseList);
            request.setAttribute("currentPage", page);
            request.setAttribute("totalPages", totalPages);
            request.setAttribute("totalRecords", totalRecords);
            request.setAttribute("selectedStatus", statusStr);
            request.setAttribute("searchKeyword", searchKeyword);

            request.getRequestDispatcher("/views/purchase/list.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 处理添加采购单页面
    private void handleAddPurchase(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            Connection conn = GokJDBCUtil.getConnection();

            // 获取供应商列表
            List<Supplier> suppliers = getSuppliers(conn);
            request.setAttribute("suppliers", suppliers);

            // 获取仓库列表
            List<Warehouse> warehouses = getWarehouses(conn);
            request.setAttribute("warehouses", warehouses);

            // 获取茶叶列表
            List<Tea> teas = getTeas(conn);
            request.setAttribute("teas", teas);
            System.out.println( teas);

            conn.close();

            request.getRequestDispatcher("/views/purchase/add.jsp").forward(request, response);

        } catch (SQLException e) {
            e.printStackTrace();
            response.sendError(HttpServletResponse.SC_INTERNAL_SERVER_ERROR, "数据库错误");
        }
    }

    // 处理保存采购单
    private void handleSavePurchase(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            Connection conn = GokJDBCUtil.getConnection();
            conn.setAutoCommit(false);

            // 获取表单数据
            int supplierId = Integer.parseInt(request.getParameter("supplierId"));
            int warehouseId = Integer.parseInt(request.getParameter("warehouseId"));
            String remark = request.getParameter("remark");

            // 获取采购项数据
            String[] teaIds = request.getParameterValues("teaId");
            String[] quantities = request.getParameterValues("quantity");
            String[] unitPrices = request.getParameterValues("unitPrice");

            if (teaIds == null || teaIds.length == 0) {
                response.sendRedirect(request.getContextPath() + "/purchase/add?error=no_items");
                return;
            }

            // 计算总金额
            double totalAmount = 0.0;
            for (int i = 0; i < teaIds.length; i++) {
                double quantity = Double.parseDouble(quantities[i]);
                double unitPrice = Double.parseDouble(unitPrices[i]);
                totalAmount += quantity * unitPrice;
            }

            // 生成采购单号
            String purchaseNo = generatePurchaseNo();

            // 插入采购单
            String purchaseSql = "INSERT INTO purchase (supplier_id, warehouse_id, purchase_no, total_amount, status) VALUES (?, ?, ?, ?, 0)";
            PreparedStatement purchaseStmt = conn.prepareStatement(purchaseSql, PreparedStatement.RETURN_GENERATED_KEYS);
            purchaseStmt.setInt(1, supplierId);
            purchaseStmt.setInt(2, warehouseId);
            purchaseStmt.setString(3, purchaseNo);
            purchaseStmt.setDouble(4, totalAmount);

            int result = purchaseStmt.executeUpdate();
            if (result <= 0) {
                conn.rollback();
                response.sendRedirect(request.getContextPath() + "/purchase/add?error=save_failed");
                return;
            }

            // 获取生成的采购单ID
            ResultSet generatedKeys = purchaseStmt.getGeneratedKeys();
            int purchaseId = 0;
            if (generatedKeys.next()) {
                purchaseId = generatedKeys.getInt(1);
            }
            generatedKeys.close();
            purchaseStmt.close();

            // 插入采购项
            String itemSql = "INSERT INTO purchase_item (purchase_id, tea_id, quantity, unit_price) VALUES (?, ?, ?, ?)";
            PreparedStatement itemStmt = conn.prepareStatement(itemSql);

            for (int i = 0; i < teaIds.length; i++) {
                int teaId = Integer.parseInt(teaIds[i]);
                double quantity = Double.parseDouble(quantities[i]);
                double unitPrice = Double.parseDouble(unitPrices[i]);

                itemStmt.setInt(1, purchaseId);
                itemStmt.setInt(2, teaId);
                itemStmt.setDouble(3, quantity);
                itemStmt.setDouble(4, unitPrice);
                itemStmt.addBatch();
            }

            itemStmt.executeBatch();
            itemStmt.close();

            // 更新库存
            updateInventoryAfterPurchase(conn, warehouseId, teaIds, quantities);

            conn.commit();
            conn.close();

            response.sendRedirect(request.getContextPath() + "/purchase/list?success=add");

        } catch (Exception e) {
            e.printStackTrace();
            response.sendRedirect(request.getContextPath() + "/purchase/add?error=system_error");
        }
    }

    // 更新库存（采购后）
    private void updateInventoryAfterPurchase(Connection conn, int warehouseId, String[] teaIds, String[] quantities)
            throws SQLException {
        System.out.println("开始更新库存 - 仓库ID: " + warehouseId);

        for (int i = 0; i < teaIds.length; i++) {
            int teaId = Integer.parseInt(teaIds[i]);
            double quantity = Double.parseDouble(quantities[i]);

            System.out.println("处理茶叶ID: " + teaId + ", 数量: " + quantity);

            // 检查该茶叶在该仓库是否已有库存记录
            String checkSql = "SELECT quantity FROM tea_inventory WHERE tea_id = ? AND warehouse_id = ?";
            PreparedStatement checkStmt = conn.prepareStatement(checkSql);
            checkStmt.setInt(1, teaId);
            checkStmt.setInt(2, warehouseId);
            ResultSet checkRs = checkStmt.executeQuery();

            if (checkRs.next()) {
                // 已有库存记录，更新数量
                double currentQuantity = checkRs.getDouble("quantity");
                double newQuantity = currentQuantity + quantity;

                System.out.println("更新现有库存 - 原数量: " + currentQuantity + ", 新增: " + quantity + ", 总数量: " + newQuantity);

                String updateSql = "UPDATE tea_inventory SET quantity = ? WHERE tea_id = ? AND warehouse_id = ?";
                PreparedStatement updateStmt = conn.prepareStatement(updateSql);
                updateStmt.setDouble(1, newQuantity);
                updateStmt.setInt(2, teaId);
                updateStmt.setInt(3, warehouseId);
                updateStmt.executeUpdate();
                updateStmt.close();

            } else {
                // 没有库存记录，新增一条
                System.out.println("新增库存记录 - 数量: " + quantity);

                String insertSql = "INSERT INTO tea_inventory (tea_id, warehouse_id, quantity) VALUES (?, ?, ?)";
                PreparedStatement insertStmt = conn.prepareStatement(insertSql);
                insertStmt.setInt(1, teaId);
                insertStmt.setInt(2, warehouseId);
                insertStmt.setDouble(3, quantity);
                insertStmt.executeUpdate();
                insertStmt.close();
            }

            checkRs.close();
            checkStmt.close();
        }

        System.out.println("库存更新完成");
    }

    // 处理编辑采购单页面
    private void handleEditPurchase(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            String idStr = request.getParameter("id");
            if (idStr == null || idStr.isEmpty()) {
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=invalid_id");
                return;
            }

            int purchaseId = Integer.parseInt(idStr);
            Connection conn = GokJDBCUtil.getConnection();

            // 获取采购单信息
            String purchaseSql = "SELECT * FROM purchase WHERE purchase_id = ?";
            PreparedStatement purchaseStmt = conn.prepareStatement(purchaseSql);
            purchaseStmt.setInt(1, purchaseId);
            ResultSet purchaseRs = purchaseStmt.executeQuery();

            Purchase purchase = null;
            if (purchaseRs.next()) {
                purchase = new Purchase();
                purchase.setPurchase_id(purchaseRs.getInt("purchase_id"));
                purchase.setSupplier_id(purchaseRs.getInt("supplier_id"));
                purchase.setWarehouse_id(purchaseRs.getInt("warehouse_id"));
                purchase.setPurchase_no(purchaseRs.getString("purchase_no"));
                purchase.setTotal_amount(purchaseRs.getDouble("total_amount"));
                purchase.setStatus(purchaseRs.getInt("status"));
            }
            purchaseRs.close();
            purchaseStmt.close();

            if (purchase == null) {
                conn.close();
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=not_found");
                return;
            }

            // 检查状态是否可编辑
            if (purchase.getStatus() != 0) {
                conn.close();
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=cannot_edit");
                return;
            }

            // 获取采购单明细
            String itemsSql = "SELECT pi.*, t.tea_name FROM purchase_item pi " +
                             "LEFT JOIN tea t ON pi.tea_id = t.tea_id " +
                             "WHERE pi.purchase_id = ?";
            PreparedStatement itemsStmt = conn.prepareStatement(itemsSql);
            itemsStmt.setInt(1, purchaseId);
            ResultSet itemsRs = itemsStmt.executeQuery();

            List<PurchaseItem> purchaseItems = new ArrayList<>();
            while (itemsRs.next()) {
                PurchaseItem item = new PurchaseItem();
                item.setItem_id(itemsRs.getInt("item_id"));
                item.setPurchase_id(itemsRs.getInt("purchase_id"));
                item.setTea_id(itemsRs.getInt("tea_id"));
                item.setQuantity(itemsRs.getDouble("quantity"));
                item.setUnit_price(itemsRs.getDouble("unit_price"));

                // 设置茶叶信息
                Tea tea = new Tea();
                tea.setTea_name(itemsRs.getString("tea_name"));
                item.setTea(tea);

                purchaseItems.add(item);
            }
            itemsRs.close();
            itemsStmt.close();

            // 获取供应商列表
            List<Supplier> suppliers = getSuppliers(conn);
            request.setAttribute("suppliers", suppliers);

            // 获取仓库列表
            List<Warehouse> warehouses = getWarehouses(conn);
            request.setAttribute("warehouses", warehouses);

            // 获取茶叶列表
            List<Tea> teas = getTeas(conn);
            request.setAttribute("teas", teas);

            conn.close();

            request.setAttribute("purchase", purchase);
            request.setAttribute("purchaseItems", purchaseItems);

            request.getRequestDispatcher("/views/purchase/edit.jsp").forward(request, response);

        } catch (Exception e) {
            e.printStackTrace();
            response.sendRedirect(request.getContextPath() + "/purchase/list?error=system_error");
        }
    }

    // 处理采购单详情
    private void handlePurchaseDetail(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // 在 PurchaseServlet 中补充 handlePurchaseDetail 方法：
        try {
            String idStr = request.getParameter("id");
            if (idStr == null || idStr.isEmpty()) {
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=invalid_id");
                return;
            }

            int purchaseId = Integer.parseInt(idStr);
            Connection conn = GokJDBCUtil.getConnection();

            // 查询采购单主表信息
            String purchaseSql = "SELECT p.*, s.supplier_name, w.warehouse_name " +
                    "FROM purchase p " +
                    "LEFT JOIN supplier s ON p.supplier_id = s.supplier_id " +
                    "LEFT JOIN warehouse w ON p.warehouse_id = w.warehouse_id " +
                    "WHERE p.purchase_id = ?";
            PreparedStatement purchaseStmt = conn.prepareStatement(purchaseSql);
            purchaseStmt.setInt(1, purchaseId);
            ResultSet purchaseRs = purchaseStmt.executeQuery();

            Purchase purchase = null;
            if (purchaseRs.next()) {
                purchase = new Purchase();
                purchase.setPurchase_id(purchaseRs.getInt("purchase_id"));
                purchase.setSupplier_id(purchaseRs.getInt("supplier_id"));
                purchase.setWarehouse_id(purchaseRs.getInt("warehouse_id"));
                purchase.setPurchase_no(purchaseRs.getString("purchase_no"));
                purchase.setTotal_amount(purchaseRs.getDouble("total_amount"));
                purchase.setStatus(purchaseRs.getInt("status"));
                // 补充关联对象信息
                Supplier supplier = new Supplier();
                supplier.setSupplier_name(purchaseRs.getString("supplier_name"));
                purchase.setSupplier(supplier);
                Warehouse warehouse = new Warehouse();
                warehouse.setWarehouse_name(purchaseRs.getString("warehouse_name"));
                purchase.setWarehouse(warehouse);
            }
            purchaseRs.close();
            purchaseStmt.close();

            if (purchase == null) {
                conn.close();
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=not_found");
                return;
            }

            // 查询采购单明细（茶叶、数量、单价等）
            String itemsSql = "SELECT pi.*, t.tea_name " +
                    "FROM purchase_item pi " +
                    "LEFT JOIN tea t ON pi.tea_id = t.tea_id " +
                    "WHERE pi.purchase_id = ?";
            PreparedStatement itemsStmt = conn.prepareStatement(itemsSql);
            itemsStmt.setInt(1, purchaseId);
            ResultSet itemsRs = itemsStmt.executeQuery();

            List<PurchaseItem> purchaseItems = new ArrayList<>();
            while (itemsRs.next()) {
                PurchaseItem item = new PurchaseItem();
                item.setItem_id(itemsRs.getInt("item_id"));
                item.setPurchase_id(itemsRs.getInt("purchase_id"));
                item.setTea_id(itemsRs.getInt("tea_id"));
                item.setQuantity(itemsRs.getDouble("quantity"));
                item.setUnit_price(itemsRs.getDouble("unit_price"));
                // 关联茶叶名称
                Tea tea = new Tea();
                tea.setTea_name(itemsRs.getString("tea_name"));
                item.setTea(tea);
                purchaseItems.add(item);
            }
            itemsRs.close();
            itemsStmt.close();

            conn.close();

            // 传递数据到详情页
            request.setAttribute("purchase", purchase);
            request.setAttribute("purchaseItems", purchaseItems);
            request.getRequestDispatcher("/views/purchase/detail.jsp").forward(request, response);

        } catch (Exception e) {
            e.printStackTrace();
            response.sendRedirect(request.getContextPath() + "/purchase/list?error=system_error");
        }
    }


    // 处理供应商列表
    private void handleSupplierList(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO: 实现供应商管理功能
        response.sendRedirect(request.getContextPath() + "/purchase/list");
    }

    // 处理仓库列表
    private void handleWarehouseList(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO: 实现仓库管理功能
        response.sendRedirect(request.getContextPath() + "/purchase/list");
    }

    // 处理茶叶列表
    private void handleTeaList(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO: 实现茶叶管理功能
        response.sendRedirect(request.getContextPath() + "/purchase/list");
    }

    // 处理更新采购单
    private void handleUpdatePurchase(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            Connection conn = GokJDBCUtil.getConnection();
            conn.setAutoCommit(false);

            // 获取表单数据
            int purchaseId = Integer.parseInt(request.getParameter("purchaseId"));
            int supplierId = Integer.parseInt(request.getParameter("supplierId"));
            int warehouseId = Integer.parseInt(request.getParameter("warehouseId"));

            // 获取采购项数据
            String[] teaIds = request.getParameterValues("teaId");
            String[] quantities = request.getParameterValues("quantity");
            String[] unitPrices = request.getParameterValues("unitPrice");

            if (teaIds == null || teaIds.length == 0) {
                response.sendRedirect(request.getContextPath() + "/purchase/edit?id=" + purchaseId + "&error=no_items");
                return;
            }

            // 计算总金额
            double totalAmount = 0.0;
            for (int i = 0; i < teaIds.length; i++) {
                double quantity = Double.parseDouble(quantities[i]);
                double unitPrice = Double.parseDouble(unitPrices[i]);
                totalAmount += quantity * unitPrice;
            }

            // 更新采购单
            String purchaseSql = "UPDATE purchase SET supplier_id = ?, warehouse_id = ?, total_amount = ? WHERE purchase_id = ? AND status = 0";
            PreparedStatement purchaseStmt = conn.prepareStatement(purchaseSql);
            purchaseStmt.setInt(1, supplierId);
            purchaseStmt.setInt(2, warehouseId);
            purchaseStmt.setDouble(3, totalAmount);
            purchaseStmt.setInt(4, purchaseId);

            int result = purchaseStmt.executeUpdate();
            if (result <= 0) {
                conn.rollback();
                response.sendRedirect(request.getContextPath() + "/purchase/edit?id=" + purchaseId + "&error=update_failed");
                return;
            }
            purchaseStmt.close();

            // 删除原有采购项
            String deleteItemsSql = "DELETE FROM purchase_item WHERE purchase_id = ?";
            PreparedStatement deleteStmt = conn.prepareStatement(deleteItemsSql);
            deleteStmt.setInt(1, purchaseId);
            deleteStmt.executeUpdate();
            deleteStmt.close();

            // 插入新的采购项
            String itemSql = "INSERT INTO purchase_item (purchase_id, tea_id, quantity, unit_price) VALUES (?, ?, ?, ?)";
            PreparedStatement itemStmt = conn.prepareStatement(itemSql);

            for (int i = 0; i < teaIds.length; i++) {
                int teaId = Integer.parseInt(teaIds[i]);
                double quantity = Double.parseDouble(quantities[i]);
                double unitPrice = Double.parseDouble(unitPrices[i]);

                itemStmt.setInt(1, purchaseId);
                itemStmt.setInt(2, teaId);
                itemStmt.setDouble(3, quantity);
                itemStmt.setDouble(4, unitPrice);
                itemStmt.addBatch();
            }

            itemStmt.executeBatch();
            itemStmt.close();

            conn.commit();
            conn.close();

            response.sendRedirect(request.getContextPath() + "/purchase/list?success=update");

        } catch (Exception e) {
            e.printStackTrace();
            response.sendRedirect(request.getContextPath() + "/purchase/list?error=system_error");
        }
    }

    // 处理删除采购单
    private void handleDeletePurchase(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        try {
            String idStr = request.getParameter("id");
            if (idStr == null || idStr.isEmpty()) {
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=invalid_id");
                return;
            }

            int purchaseId = Integer.parseInt(idStr);
            Connection conn = GokJDBCUtil.getConnection();
            conn.setAutoCommit(false);

            // 检查采购单状态，只有待审核状态才能删除
            String checkSql = "SELECT status FROM purchase WHERE purchase_id = ?";
            PreparedStatement checkStmt = conn.prepareStatement(checkSql);
            checkStmt.setInt(1, purchaseId);
            ResultSet checkRs = checkStmt.executeQuery();

            if (!checkRs.next()) {
                conn.rollback();
                conn.close();
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=not_found");
                return;
            }

            int status = checkRs.getInt("status");
            checkRs.close();
            checkStmt.close();

            if (status != 0) {
                conn.rollback();
                conn.close();
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=cannot_delete");
                return;
            }

            // 删除采购单明细
            String deleteItemsSql = "DELETE FROM purchase_item WHERE purchase_id = ?";
            PreparedStatement deleteItemsStmt = conn.prepareStatement(deleteItemsSql);
            deleteItemsStmt.setInt(1, purchaseId);
            deleteItemsStmt.executeUpdate();
            deleteItemsStmt.close();

            // 删除采购单
            String deletePurchaseSql = "DELETE FROM purchase WHERE purchase_id = ?";
            PreparedStatement deletePurchaseStmt = conn.prepareStatement(deletePurchaseSql);
            deletePurchaseStmt.setInt(1, purchaseId);
            int result = deletePurchaseStmt.executeUpdate();
            deletePurchaseStmt.close();

            if (result > 0) {
                conn.commit();
                response.sendRedirect(request.getContextPath() + "/purchase/list?success=delete");
            } else {
                conn.rollback();
                response.sendRedirect(request.getContextPath() + "/purchase/list?error=delete_failed");
            }

            conn.close();

        } catch (Exception e) {
            e.printStackTrace();
            response.sendRedirect(request.getContextPath() + "/purchase/list?error=system_error");
        }
    }

    // 处理更新状态
    private void handleUpdateStatus(HttpServletRequest request, HttpServletResponse response)
            throws ServletException, IOException {
        // TODO: 实现状态更新功能
        response.sendRedirect(request.getContextPath() + "/purchase/list");
    }

    // 生成采购单号
    private String generatePurchaseNo() {
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateStr = sdf.format(new Date());
        long timestamp = System.currentTimeMillis() % 10000;
        return "PO" + dateStr + String.format("%04d", timestamp);
    }

    // 获取供应商列表
    private List<Supplier> getSuppliers(Connection conn) throws SQLException {
        List<Supplier> suppliers = new ArrayList<>();
        String sql = "SELECT * FROM supplier WHERE status = 1 ORDER BY supplier_name";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Supplier supplier = new Supplier();
            supplier.setSupplier_id(rs.getInt("supplier_id"));
            supplier.setSupplier_name(rs.getString("supplier_name"));
            supplier.setPhone(rs.getString("phone"));
            supplier.setEmail(rs.getString("email"));
            supplier.setAddress(rs.getString("address"));
            supplier.setStatus(rs.getInt("status"));
            suppliers.add(supplier);
        }

        rs.close();
        stmt.close();
        return suppliers;
    }

    // 获取仓库列表
    private List<Warehouse> getWarehouses(Connection conn) throws SQLException {
        List<Warehouse> warehouses = new ArrayList<>();
        String sql = "SELECT * FROM warehouse ORDER BY warehouse_name";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Warehouse warehouse = new Warehouse();
            warehouse.setWarehouse_id(rs.getInt("warehouse_id"));
            warehouse.setWarehouse_name(rs.getString("warehouse_name"));
            warehouse.setLocation(rs.getString("location"));
            warehouse.setCapacity(rs.getDouble("capacity"));
            warehouse.setStatus(rs.getInt("status"));
            warehouses.add(warehouse);
        }

        rs.close();
        stmt.close();
        return warehouses;
    }

    // 获取茶叶列表
    private List<Tea> getTeas(Connection conn) throws SQLException {
        List<Tea> teas = new ArrayList<>();
        String sql = "SELECT t.*, tc.category_name FROM tea t " +
                    "LEFT JOIN tea_category tc ON t.tea_category_id = tc.category_id " +
                    "ORDER BY t.tea_name";
        PreparedStatement stmt = conn.prepareStatement(sql);
        ResultSet rs = stmt.executeQuery();

        while (rs.next()) {
            Tea tea = new Tea();
            tea.setTea_id(rs.getInt("tea_id"));
            tea.setTea_category_id(rs.getInt("tea_category_id"));
            tea.setTea_name(rs.getString("tea_name"));
            tea.setDescription(rs.getString("description"));
            tea.setPrice(rs.getDouble("price"));
            tea.setImage(rs.getString("image"));
            tea.setUnit(rs.getString("unit"));
            teas.add(tea);
        }

        rs.close();
        stmt.close();
        return teas;
    }
}